const connection = require('../../sql');
const bcrypt = require('bcryptjs');
const ConfigService = require('../../service/system/config.service');
class UserService {
	async getUserInfo(id) {
		// 查询用户信息
		const userSql = `
		  SELECT 
			id, 
			username, 
			phone, 
			nickname, 
			logo, 
			age, 
			sex, 
			id_card AS idCard, 
			email, 
			account_status_cd AS accountStatusCd, 
			user_tag_cd AS userTagCd, 
			last_login_time AS lastLoginTime, 
			create_time AS createTime, 
			update_time AS updateTime, 
			del_flag AS delFlag
		  FROM sys_user 
		  WHERE id = ? AND del_flag = 'F'
		`;
		const [userRows] = await connection.execute(userSql, [id]);

		if (!userRows.length) {
			return null;
		}

		const user = userRows[0];

		// 查询部门信息
		const [deptInfo, deptIds] = await this.getDeptInfo(id);
		user.deptInfo = deptInfo;
		user.deptIds = deptIds;

		// 查询角色信息
		const [roleInfo, roleIds] = await this.getRoleInfo(id);
		user.roleInfo = roleInfo;
		user.roleIds = roleIds;

		return user;
	}
	async getDeptTree() {
		const deptSql = `
		  SELECT id, pid, deep, sort, name
		  FROM sys_dept
		  WHERE del_flag = 'F'
		  ORDER BY sort ASC
		`;
		const [deptRows] = await connection.execute(deptSql);

		const userDeptSql = `
		  SELECT dept_id, COUNT(DISTINCT sys_user.id) as user_count
		  FROM sys_user_dept
		  JOIN sys_user ON sys_user_dept.user_id = sys_user.id
		  WHERE sys_user.del_flag = 'F'
		  GROUP BY dept_id
		`;
		const [userDeptRows] = await connection.execute(userDeptSql);

		const totalUserSql = `
		  SELECT COUNT(*) as total
		  FROM sys_user
		  WHERE del_flag = 'F'
		`;
		const unsetUserSql = `
		  SELECT COUNT(DISTINCT u.id) as unset_count
		  FROM sys_user u
		  LEFT JOIN sys_user_dept ud ON u.id = ud.user_id
		  WHERE u.del_flag = 'F' AND ud.user_id IS NULL
		`;
		const [totalUserResult] = await connection.execute(totalUserSql);
		const [unsetUserResult] = await connection.execute(unsetUserSql);

		const totalUsers = totalUserResult[0].total;
		const unsetUsers = unsetUserResult[0].unset_count;

		const deptUserMap = {};
		userDeptRows.forEach((row) => {
			deptUserMap[row.dept_id] = row.user_count;
		});

		const deptTree = this.buildDirectDeptTree(deptRows, 0, deptUserMap);

		return [
			{ id: -1, pid: '', deep: '', sort: '', children: '', name: '全部', userTotal: totalUsers },
			{ id: -2, pid: '', deep: '', sort: '', children: '', name: '未设置部门', userTotal: unsetUsers },
			...deptTree,
		];
	}

	buildDirectDeptTree(deptRows, parentId, deptUserMap) {
		const nodes = [];
		for (const row of deptRows) {
			if (row.pid === parentId) {
				const children = this.buildDirectDeptTree(deptRows, row.id, deptUserMap);
				nodes.push({
					id: row.id,
					pid: row.pid,
					deep: row.deep,
					sort: row.sort,
					children: children.length ? children : [],
					name: row.name,
					userTotal: deptUserMap[row.id] || 0,
				});
			}
		}
		return nodes;
	}

	async saveUser(params) {
		const salt = bcrypt.genSaltSync(10);
		const configValue = await ConfigService.getConfigValue('sys.user.initPwd');
		const hashedPwd = bcrypt.hashSync(configValue, salt);
		const statement = `
      INSERT INTO sys_user (username, pwd, nickname, phone, email, sex, birthday, id_card, logo, create_id, create_time, account_status_cd)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
		const [result] = await connection.execute(statement, [
			params.username,
			hashedPwd,
			params.nickname,
			params.phone,
			params.email || null,
			params.sex ?? 0,
			params.birthday || null,
			params.idCard || null,
			params.logo || null,
			params.userId,
			new Date(),
			params.accountStatusCd,
		]);
		return result;
	}

	async resetPassword(id) {
		try {
			const configValue = await ConfigService.getConfigValue('sys.user.initPwd');
			// 加密密码
			const salt = bcrypt.genSaltSync(10);
			const hashedPwd = bcrypt.hashSync(configValue, salt);
			// 更新用户密码
			const updateSql = `
			UPDATE sys_user 
			SET pwd = ?, update_time = NOW() 
			WHERE id = ?
		  `;
			const [result] = await connection.execute(updateSql, [hashedPwd, id]);
			if (result.affectedRows === 0) {
				throw new Error('用户不存在或已被删除');
			}
			return result;
		} catch (error) {
			throw error;
		}
	}

	async listUsers({ isThisDeep, deptId, page, limit }) {
		const offset = (page - 1) * limit;
		let whereClause = 'WHERE u.del_flag = "F"';
		const params = [];

		if (deptId !== -1) {
			if (deptId === -2) {
				whereClause += ' AND ud.dept_id IS NULL';
			} else {
				if (isThisDeep) {
					whereClause += ' AND ud.dept_id = ?';
					params.push(deptId);
				} else {
					whereClause += `
				AND ud.dept_id IN (
				  SELECT descendant_id
				  FROM sys_dept_closure
				  WHERE ancestor_id = ?
				)
			  `;
					params.push(deptId);
				}
			}
		}

		// 查询总数（去重）
		const countSql = `
		  SELECT COUNT(DISTINCT u.id) as total
		  FROM sys_user u
		  LEFT JOIN sys_user_dept ud ON u.id = ud.user_id
		  ${whereClause}
		`;
		const [countResult] = await connection.execute(countSql, params);

		// 查询用户列表（去重）
		const listSql = `
		  SELECT DISTINCT
			u.id, u.username, u.phone, u.nickname, u.logo, u.age, u.sex, u.id_card as idCard, 
			u.email, u.account_status_cd as accountStatusCd, u.user_tag_cd as userTagCd, 
			u.last_login_time as lastLoginTime, u.create_time as createTime, u.update_time as updateTime, 
			u.del_flag as delFlag
		  FROM sys_user u
		  LEFT JOIN sys_user_dept ud ON u.id = ud.user_id
		  ${whereClause}
		  ORDER BY u.create_time ASC
		  LIMIT ? OFFSET ?
		`;
		const listParams = [...params, String(limit), String(offset)];
		const [rows] = await connection.execute(listSql, listParams);

		// 获取部门和角色信息
		for (const row of rows) {
			const [deptInfo, deptIds] = await this.getDeptInfo(row.id);
			const [roleInfo, roleIds] = await this.getRoleInfo(row.id);
			row.deptInfo = deptInfo;
			row.deptIds = deptIds;
			row.roleInfo = roleInfo;
			row.roleIds = roleIds;
		}

		return {
			current: Number(page),
			limit,
			totalPage: Math.ceil(countResult[0].total / limit),
			total: countResult[0].total,
			rows,
		};
	}

	async getDeptInfo(userId) {
		const sql = `
		  SELECT d.id, d.name
		  FROM sys_user_dept ud
		  JOIN sys_dept d ON ud.dept_id = d.id
		  WHERE ud.user_id = ? AND d.del_flag = 'F'
		`;
		const [rows] = await connection.execute(sql, [userId]);
		const deptNames = rows.map((row) => `${row.id}:${row.name}`).join(','); // 格式为 "id:名称"
		const deptIds = rows.map((row) => row.id).join(','); // 逗号分隔的 ID
		return [deptNames || '', deptIds || ''];
	}

	async getRoleInfo(userId) {
		const sql = `
		  SELECT r.id, r.role_name
		  FROM sys_user_role ur
		  JOIN sys_role r ON ur.role_id = r.id
		  WHERE ur.user_id = ? AND r.del_flag = 'F'
		`;
		const [rows] = await connection.execute(sql, [userId]);
		const roleInfo = rows.map((row) => `${row.id}:${row.role_name}`).join(',');
		const roleIds = rows.map((row) => row.id).join(',');
		return [roleInfo, roleIds];
	}

	async getUserRoles(userId) {
		// 查询用户已关联的角色 ID
		const selectSql = `
		  SELECT role_id
		  FROM sys_user_role
		  WHERE user_id = ?
		`;
		const [selectRows] = await connection.execute(selectSql, [userId]);
		const selectIds = selectRows.map((row) => row.role_id);

		// 查询所有未删除的角色
		const roleSql = `
		  SELECT id, role_name as roleName
		  FROM sys_role
		  WHERE del_flag = 'F'
		  ORDER BY create_time ASC
		`;
		const [roleRows] = await connection.execute(roleSql);

		const roleInfoVOS = roleRows.map((row) => ({
			id: row.id,
			roleName: row.roleName,
		}));

		return {
			selectIds,
			roleInfoVOS,
		};
	}

	async saveUserRoles(userId, roleIds) {
		const conn = await connection.getConnection();

		try {
			await conn.beginTransaction();

			// 删除用户现有的角色关联
			const deleteSql = `DELETE FROM sys_user_role WHERE user_id = ?`;
			await conn.execute(deleteSql, [userId]);

			// 插入新的角色关联（如果有）
			if (roleIds.length > 0) {
				const roleValues = roleIds.map((roleId) => [userId, roleId]);
				const insertSql = `INSERT INTO sys_user_role (user_id, role_id) VALUES ?`;
				await conn.query(insertSql, [roleValues]);
			}

			await conn.commit();
			return userId;
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}

	async bindUserDepts(userIds, deptIds) {
		const conn = await connection.getConnection();

		try {
			await conn.beginTransaction();

			// 删除用户现有的部门关联
			const deleteSql = `DELETE FROM sys_user_dept WHERE user_id IN (${userIds.map(() => '?').join(',')})`;
			await conn.execute(deleteSql, userIds);

			// 插入新的部门关联（如果有）
			if (deptIds.length > 0) {
				const deptValues = [];
				userIds.forEach((userId) => {
					deptIds.forEach((deptId) => {
						deptValues.push([userId, deptId]);
					});
				});
				const insertSql = `INSERT INTO sys_user_dept (user_id, dept_id) VALUES ?`;
				await conn.query(insertSql, [deptValues]);
			}

			await conn.commit();
			return userIds;
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}

	async getUserDataRoles(userId) {
		// 查询用户已关联的数据角色 ID
		const selectSql = `
		  SELECT role_id
		  FROM sys_user_data_role
		  WHERE user_id = ?
		`;
		const [selectRows] = await connection.execute(selectSql, [userId]);
		const selectIds = selectRows.map((row) => row.role_id);

		// 查询所有未删除的数据角色
		const roleSql = `
		  SELECT id, role_name as roleName
		  FROM sys_data_role
		  WHERE del_flag = 'F'
		  ORDER BY create_time ASC
		`;
		const [roleRows] = await connection.execute(roleSql);

		const roleInfoVOS = roleRows.map((row) => ({
			id: row.id,
			roleName: row.roleName,
		}));

		return {
			selectIds,
			roleInfoVOS,
		};
	}

	async saveUserDataRoles(userId, roleIds) {
		const conn = await connection.getConnection();

		try {
			await conn.beginTransaction();

			// 删除用户现有的数据角色关联
			const deleteSql = `DELETE FROM sys_user_data_role WHERE user_id = ?`;
			await conn.execute(deleteSql, [userId]);

			// 插入新的数据角色关联（如果有）
			if (roleIds.length > 0) {
				const roleValues = roleIds.map((roleId) => [userId, roleId]);
				const insertSql = `INSERT INTO sys_user_data_role (user_id, role_id) VALUES ?`;
				await conn.query(insertSql, [roleValues]);
			}

			await conn.commit();
			return userId;
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}

	async deleteUsers(ids) {
		const conn = await connection.getConnection();

		try {
			await conn.beginTransaction();

			// 软删除用户
			const updateUserSql = `
			UPDATE sys_user 
			SET del_flag = 'T', update_time = NOW()
			WHERE id IN (${ids.map(() => '?').join(',')})
		  `;
			await conn.execute(updateUserSql, ids);

			// 删除用户-部门关联
			const deleteDeptSql = `
			DELETE FROM sys_user_dept 
			WHERE user_id IN (${ids.map(() => '?').join(',')})
		  `;
			await conn.execute(deleteDeptSql, ids);

			// 删除用户-角色关联
			const deleteRoleSql = `
			DELETE FROM sys_user_role 
			WHERE user_id IN (${ids.map(() => '?').join(',')})
		  `;
			await conn.execute(deleteRoleSql, ids);

			// 删除用户-数据角色关联
			const deleteDataRoleSql = `
			DELETE FROM sys_user_data_role 
			WHERE user_id IN (${ids.map(() => '?').join(',')})
		  `;
			await conn.execute(deleteDataRoleSql, ids);

			await conn.commit();
			return ids;
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}

	async getUserById(id) {
		const conn = await connection.getConnection();
		try {
			// 查询用户信息
			const userSql = `
        SELECT 
          id, username, phone, nickname, logo, age, sex, id_card AS idCard, email, birthday,
          CAST(account_status_cd AS UNSIGNED) AS accountStatusCd, 
          user_tag_cd AS userTagCd, 
          last_login_time AS lastLoginTime, create_time AS createTime, 
          update_time AS updateTime, del_flag AS delFlag
        FROM sys_user 
        WHERE id = ? AND del_flag = 'F'
      `;
			const [userRows] = await conn.execute(userSql, [id]);

			if (!userRows.length) {
				return null; // 用户不存在
			}

			const user = userRows[0];

			// 查询部门信息
			const deptSql = `
			SELECT d.id, d.name
			FROM sys_user_dept ud
			JOIN sys_dept d ON ud.dept_id = d.id
			WHERE ud.user_id = ? AND d.del_flag = 'F'
		  `;
			const [deptRows] = await conn.execute(deptSql, [id]);
			const deptIds = deptRows.map((row) => row.id).join(',');
			const deptInfo = deptRows.map((row) => row.name).join(',');

			// 查询角色信息
			const roleSql = `
			SELECT r.id, r.role_name AS roleName
			FROM sys_user_role ur
			JOIN sys_role r ON ur.role_id = r.id
			WHERE ur.user_id = ? AND r.del_flag = 'F'
		  `;
			const [roleRows] = await conn.execute(roleSql, [id]);
			const roleIds = roleRows.map((row) => row.id).join(',');
			const roleInfo = roleRows.map((row) => row.roleName).join(',');

			// 处理 NULL 值，转换为空字符串
			const result = {
				...user,
				deptInfo: deptInfo || '',
				deptIds: deptIds || '',
				roleInfo: roleInfo || '',
				roleIds: roleIds || '',
			};
			Object.keys(result).forEach((key) => {
				if (result[key] === null) result[key] = '';
			});

			return result;
		} finally {
			conn.release();
		}
	}

	async updateUser({ birthday, id, username, phone, nickname, logo, age, sex, idCard, email, updateId, accountStatusCd }) {
		const sql = `
		  UPDATE sys_user 
		  SET 
			username = ?, 
			phone = ?, 
			nickname = ?, 
			logo = ?, 
			age = ?, 
			sex = ?, 
			id_card = ?, 
			email = ?, 
			update_id = ?, 
			update_time = NOW(), 
			account_status_cd = ?,
			birthday = ?
		  WHERE id = ? AND del_flag = 'F'
		`;
		const params = [
			username,
			phone || null,
			nickname || '',
			logo || '',
			age || null,
			sex ?? null,
			idCard || null,
			email || null,
			updateId,
			accountStatusCd || '',
			birthday,
			id,
		];

		const [result] = await connection.execute(sql, params);

		if (result.affectedRows === 0) {
			return null;
		}

		return { id };
	}

	//修改密码
	//查询原密码是否正确
	async checkPassword(id) {
		const sql = `
			SELECT pwd FROM sys_user WHERE id = ? AND del_flag = 'F'
		`;
		const [result] = await connection.execute(sql, [id]);

		return result[0];
	}
	//修改密码
	async updatePassword({ id, newPwd }) {
		const sql = `
			UPDATE sys_user SET pwd = ? WHERE id = ? AND del_flag = 'F'
		`;
		// 加密密码
		const salt = bcrypt.genSaltSync(10);
		const hashedPwd = bcrypt.hashSync(newPwd, salt);
		const [result] = await connection.execute(sql, [hashedPwd, id]);
		if (result.affectedRows === 0) {
			return null;
		}
		return { id };
	}
}

module.exports = new UserService();
